'''

@author: wyndem
@Emil:   wyndem@qq.com
@FileName: vpnUtil.py
@Time: 2019/3/21 15:26
@Software: PyCharm
@Description: 

'''
import copy

from db import POOL


def findByName(name):
    """
    通过用户名查找
    :param name:
    :return:
    """
    conn = POOL.connection()
    cursor = conn.cursor()
    sql = """
       SELECT * FROM `openvpn` WHERE iuser=%s
      """
    cursor.execute(sql, (name))
    data = cursor.fetchone()
    cursor.close()
    conn.close()
    return data


def creater(name, pwd):
    """
    创建用户
    :param name:
    :return:
    """
    conn = POOL.connection()
    cursor = conn.cursor()
    sql = """
            INSERT INTO `Kyml`.`openvpn`
                    (`iuser`, `isent`, `irecv`, `maxll`, `pass`, `i`, `starttime`, `endtime`, `mail`, `dlid`, `tj_u`, `online`, `last_ip`, `area`, `isp`, `client`, `remote_port`, `xieyi`, `login_time`, `line_id`, `tian`) 
                VALUES 
                    (%s, 0, 0, 5242880, %s, 1, 1551334648, 1651437739, '', 0, 0, 0, '', '', '', '', 0, '', 0, 0, 99);

      """
    cursor.execute(sql, (name, pwd))
    author_id = cursor.lastrowid
    conn.commit()
    id = copy.deepcopy(author_id)
    cursor.close()
    conn.close()
    return id


def updataMaxll(name,maxll):
    """
    增加用户流量
    :return:
    """
    #获取用户原本的流量
    user=findByName(name)
    ll=user[4]

    conn = POOL.connection()
    cursor = conn.cursor()

    sql = """
               UPDATE `Kyml`.`openvpn` SET `maxll` = %s WHERE `id` = %s
             """

    cursor.execute(sql,(int(ll)+int(maxll),user[0]))
    conn.commit()
    cursor.close()
    conn.close()




if __name__ == '__main__':
    # r = findByName('4126123')
    # print(r)
    # a=creater('草泥马', '123')
    # print(a)
    updataMaxll('草泥马',1)
